
package manager;

import beans.person.*;
import commons.Constants;
import commons.ExceptionCLLAMB;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;



/**
 * 'insertPerson
 * 'insert
 * @author xmarti
 */
public class ManagerPerson {
    ManagerConnection manager;
    Connection connection;
    public ManagerPerson() throws ExceptionCLLAMB{
        this.manager= new ManagerConnection();
        this.manager.openConnection();
        this.connection=manager.getConnection();
    }
    public ManagerPerson(ManagerConnection pManager){
        this.manager=pManager;
        this.connection=manager.getConnection();
    }
    /*
     * state boolean NOT NULL,  -- A Actiu i B Bloquejat
    documentType character(3) NOT NULL, -- NIF, NIE o ALT
    document character varying(16) NOT NULL,
    name character varying(32) NOT NULL,
    surname1 character varying(32) NOT NULL,
    surname2 character varying(32),
    address character varying(200),
    zipCode character varying(10),
    city character varying(100), 
    country character varying (50),    
    dateOn date NOT NULL,    
    dateOff date,    
    CONSTRAINT pk_person PRIMARY KEY (idPerson)    
     */
    public int insertPerson (Person p) throws ExceptionCLLAMB{
        int id=0;
        String sql ="INSERT INTO person(state,documentType,document,name,surmane1,surname2,address,zipCode,city,country"
                + "dateOn) values(?,?,?,?,?,?,?,?,?,?,?) RETURNING idperson";
       PreparedStatement ps=manager.createPreparedStatement(sql);
       try{
           ps.setBoolean(1, true);
           ps.setString(2,p.getDocumentType());
           ps.setString(3,p.getDocument());
           ps.setString(4,p.getName());
           ps.setString(5,p.getSurname1());
           ps.setString(6,p.getSurname2());
           ps.setString(7,p.getAddress());
           ps.setString(8,p.getZipCode());
           ps.setString(9,p.getCity());
           ps.setString(10,p.getCountry());
           ps.setDate(11,new java.sql.Date(System.currentTimeMillis()));
           id=ps.executeUpdate();
           
       }catch (SQLException sqle){
           //cal definir les excepcions correctes
           throw new ExceptionCLLAMB(ExceptionCLLAMB.ERRCONNECTDB);
       }
       
        
         return id;        
    }    
    public int insertUser(User u){
        int id=0;        
        return id;        
    }
    public int insertCompany(Company c){
        int id=0;        
        return id;        
    }
    public int insertProvider (Provider p){
        int id=0;        
        return id;            
    }
    public int insertCustomer (Customer c){
        int id=0;        
        return id;            
    }
    public List<User> getUsers(int c) throws ExceptionCLLAMB{
        List<User> users= new ArrayList<User>();
        
        Person person=null;
        
        
        Statement statement=manager.createStatement();
        String sql="SELECT u.iduser,u.idperson,u.password, u.level FROM usercllamb u ";
        if(c!=Constants.companyCLLAMB){
            sql=sql + " where u.idcompany="+c;                    
        }
        sql=sql+" order by u.level";
        try {
            ResultSet rs=statement.executeQuery(sql);
            while (rs.next()){
                person=getPerson(rs.getInt(2));                              
                users.add(new User(person.getName(),person.getSurname1(),person.getSurname2(),person.getDocumentType(),
                        person.getDocument(),person.getAddress(),person.getCity(),person.getCountry(),person.getZipCode(),person.getPhone(),person.getEmail(),
                        rs.getString(1),rs.getString(3),rs.getInt(4)));
            }
        } catch (SQLException ex) {
           throw new ExceptionCLLAMB(ExceptionCLLAMB.ERRSELECTSQL);
        }
        return users;
    }     
     public User getUser(String name, String password) throws ExceptionCLLAMB{
                
        Person person=null;
        User user=null;        
        Statement statement=manager.createStatement();
        //String sql="SELECT u.iduser,u.idperson,u.password, u.level FROM usercllamb u "
          //      + "WHERE u.iduser='"+name+"'";                                   
        String sql="SELECT u.iduser,u.idperson,u.password, u.level FROM usercllamb u "
                + "WHERE u.iduser='"+name+"'";  
        
        try {
            ResultSet rs=statement.executeQuery(sql);
            if (rs.next()){
                person=getPerson(rs.getInt(2));                
                user = new User(person.getName(),person.getSurname1(),person.getSurname2(),person.getDocumentType(),
                        person.getDocument(),person.getAddress(),person.getCity(),person.getCountry(),person.getZipCode(),person.getPhone(),person.getEmail(),
                        rs.getString(2),rs.getString(3),rs.getInt(4));
            }
            if (user==null){
                throw new ExceptionCLLAMB("Usuari Incorrecte");
            }else{
                if (!user.getpassword().equals(password)){
                    throw new ExceptionCLLAMB("Clau Incorrecte");
                }
            }
                 
        } catch (SQLException ex) {
           throw new ExceptionCLLAMB(ExceptionCLLAMB.ERRSELECTSQL);
        }
        return user;
    }     
     private Person getPerson (int idPerson) throws ExceptionCLLAMB {
         Person person =null;
         
         //(String name, String surname1, String surname2, String documentType, String document, String address, String city, String country, String zipCode)
         String sql="SELECT name,surname1,surname2,documentType,document,address,city, country,zipcode,phone,email "                 
                 + "FROM person "
                 + "WHERE state= true and idPerson="+idPerson;
         Statement st = manager.createStatement();
         ResultSet rs=null;
         try{
             rs=st.executeQuery(sql);
             if(rs.next()){
                 person =new Person(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),
                         rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9),rs.getString(10),rs.getString(11));
             }
             rs.close();
             st.close();             
         }catch (SQLException sqle){
            try {
                if (!rs.isClosed()){ rs.close();}
                if (!st.isClosed()) {st.close();}
                
            } catch (SQLException ex) {
                throw new ExceptionCLLAMB(ExceptionCLLAMB.ERRSELECTSQL);
            }
            throw new ExceptionCLLAMB(ExceptionCLLAMB.ERRSELECTSQL);            
         }
         return person;     
     }
             
    
}
